package com.eascs.web.o2o.report.service;

import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;

import javax.inject.Inject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;

import com.alibaba.dubbo.config.annotation.Reference;
import com.eascs.app.exception.ControllerException;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.doc.excel.ExcelApiHelper;
import com.eascs.common.util.IoUtil;
import com.eascs.common.util.UuidUtil;
import com.eascs.notify.constant.ModelResult;
import com.eascs.notify.email.EmailSender;
import com.eascs.notify.email.entity.EmailContentEntity;
import com.eascs.notify.email.entity.EmailSourceSetting;
import com.eascs.notify.mail.MailNotifier;
import com.eascs.notify.mail.MailNotifyRequest;
import com.eascs.notify.mail.MailNotifyResponse;
import com.eascs.notify.mail.MailSendType;
import com.eascs.web.o2o.report.util.BankRowWriteHandler;
import com.eascs.web.o2o.report.util.DateUtil;
import com.eascs.workflow.common.entity.sys.ActOrgEntity;
import com.eascs.workflow.common.service.sys.SysOrgService;

/**
 * @ClassName: AfterLoanDataService
 * @Description:贷后的报表-O2O金融业务台账
 * @author 张庆奇
 * @date 2016年8月23日 下午2:57:49
 * @version V1.0
 * 
 * 注意：此类中的报表都已经不在使用
 */
@Service
public class AfterLoanDataService {

	protected final Logger logger = LoggerFactory.getLogger(getClass());
	@Inject
	BaseReportService baseReportService;
	@Reference
	MailNotifier mailNotifier;
	@Autowired
	private EmailSender emailSender;
	@Autowired
	EmailSourceSetting emailSourceSetting;
	
	@Reference
	SysOrgService sysOrgService;
	
	@Value("${email.toemail.dev}")
	String devmail;
	
	@Value("${email.toemail.refund}")
	String refundmail;

	
	public void makeAfterLoanExcel1(){
		logger.info("=======开始O2O金融业务台账——投放量统计表（按省区-平台-借款人明细表）报表======");
		try {
			loanDetail();			
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("=======生成O2O金融业务台账——投放量统计表（按省区-平台-借款人明细表）报表异常======");
		}
		logger.info("=======结束生成O2O金融业务台账——投放量统计表（按省区-平台-借款人明细表）报表======");
		
		
		logger.info("=======开始O2O金融业务台账——投放量统计表（按平台)报表======");
		try {
			reportPingtaiDelivery();			
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("=======生成O2O金融业务台账——投放量统计表（按平台)报表异常======");
		}
		logger.info("=======结束生成O2O金融业务台账——投放量统计表（按平台)报表======");
		
		
		/*logger.info("=======开始O2O金融业务台账——借款人账务信息明细表报表======");
		try {
			loanFinanceInfo();			
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("=======生成O2O金融业务台账——借款人账务信息明细表报表异常======");
		}
		logger.info("=======结束生成O2O金融业务台账——借款人账务信息明细表报表======");*/
		
		
	}
	
	//此报表没在使用
	public void makeAfterLoanExcel2() {
		logger.info("=======开始O2O金融业务台账——月度统计表报表======");
		try {
			monthDetail();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("=======生成O2O金融业务台账——月度统计表报表异常======");
		}
		logger.info("=======结束生成O2O金融业务台账——月度统计表报表======");

		logger.info("=======开始O2O金融业务台账——投放量统计表（按省区)报表======");
		try {
			delivery();
		} catch (Exception e) {
			e.printStackTrace();
			logger.error("=======生成O2O金融业务台账——投放量统计表（按省区)报表异常======");
		}
		logger.info("=======结束生成O2O金融业务台账——投放量统计表（按省区)报表======");

	}
			
			
	public void adapterShengByDate() {
		logger.info("=======时间段省区报表统计开始======");
		try {
			reportShengByDate("2016-01-21");
			reportShengByDate("2016-02-22");
			reportShengByDate("2016-03-07");
			reportShengByDate("2016-04-11");
			reportShengByDate("2016-04-18");
			reportShengByDate("2016-05-04");
			reportShengByDate("2016-05-10");
			reportShengByDate("2016-05-20");
			reportShengByDate("2016-05-30");
			reportShengByDate("2016-06-20");
			reportShengByDate("2016-06-30");
			reportShengByDate("2016-07-26");
		} catch (Exception e) {
			logger.info("=====时间段省区报表统计数据异常========");
		}	
		logger.info("=======时间段省区报表统计完成======");
	}
	
	
	public void adapter_refund() {
		logger.info("=======日增放款数据开始======");
		try {
			addData();//日增放款数据
		} catch (Exception e) {
			logger.info("=====日增放款数据异常========");
		}	
		logger.info("=======日增放款数据完成======");
	}
	
	public void adapter_month() {
		reportShengZdd();
		reportPersonal();
	}
	
	
	
	/*
	 * 发送邮件--日邮件
	 */
	public void job_sendMail() {
		logger.info("=============开始发送邮件--日邮件===============");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		ArrayList<String> listDirectories=new ArrayList<String>();
		String date = sdf.format(new Date());
		if(listDirectories==null||listDirectories.size()==0){
			IoUtil.createDirectorieIfMissing(new File(emailSourceSetting.getFilepath()+"/"+date).getParentFile());
			listDirectories.add(emailSourceSetting.getFilepath()+"/"+date); 
		}
		mailSendOneByOne(listDirectories, "O2O金融报表-",date,true);
		//mailSendOneByOneNew(listDirectories, "O2O金融报表-",date,true);
		logger.info("=============结束发送邮件--日邮件===============");
	}
	
	
	public void sendMail_our() {
		logger.info("=============开始发送异常邮件--日邮件===============");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		ArrayList<String> listDirectories=new ArrayList<String>();
		String date = sdf.format(new Date());
		if(listDirectories==null||listDirectories.size()==0){
			IoUtil.createDirectorieIfMissing(new File(emailSourceSetting.getFilepath()+"/"+date).getParentFile());
			listDirectories.add(emailSourceSetting.getFilepath()+"/"+date); 
		}
		mailSendOneByOne(listDirectories, "报表异常",date,false);
		//mailSendOneByOneNew(listDirectories, "报表异常",date,false);
		logger.info("=============结束发送异常邮件--日邮件===============");
	}
	
	
	
	
	
	/*
	 * 实例
	 */
	@Deprecated
	public String reportShengZdd() {
		//step1
		String statistics_day ="2015-12-31";
		String dead_day ="2016-4-30";
		String remark ="全国各省区终端贷授信情况汇总表";
		String pro = "call pro_report_sheng_zdd('"+statistics_day+"','"+dead_day+"','"+remark+"')";
	
		//step2
		String sql = "select t.itemname,t.ncye,t.dntfbs,t.dntfje,t.dnhsbs,t.dnhsje,t.bgrdkye,t.yqxj ,t.dqlxyqje,t.dqbjyqje,t.ydcje from report_sheng_zdd t where t.is_history='0'";
		String soucreFileName = "/excelTemplate/terminal-loan.xls";
		int col = 13;//excel包含的列数
		int[] arr={1,2,3,4,5,6,7,8,9,10};//需要统计的列
		int sheetIndex=0;//数据展示的表格
		int rowIndex=5;//开始显示数据的行
		int columnIndex=1;//开始显示数据的列
		//excel名称
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		String path = emailSourceSetting.getFilepath();
		StringBuffer s = new StringBuffer();
		//O2O金融终端待月度余额明细表
		//String fileAttach="/report-sheng-zdd-";
		String fileAttach="/O2O金融终端贷月度授信汇总表-";
		String outPath = s.append(path).append("/"+date).append(fileAttach).append(date).append(".xls").toString();
		excuteProReport(pro);
		write(sql, soucreFileName,outPath, col,arr,Arrays.asList("全国各省区终端贷授信情况汇总表","截止日期：2016年4月末","单位：元"),sheetIndex,rowIndex,columnIndex);
		
		//step3
		return path+"/"+date;
	}
	
	/**
	 * 生成个人终端贷报表
	 * 
	 * @return
	 */
	@Deprecated
	public String reportPersonal() {
		String sql = "SELECT LOADAPPLYCODE,LOANERNAME,CUSTOMERNAME,NAMECN,ITEMNAME,CREATEDATE,RATE,LOANTERM,REPAYMENTWAY,LOADDATE,MATDATE,GUARANTEEWAY,MANAGEFEEL,MANAGEFEE,EXISTDEPOSITL,EXISTDEPOSIT,OPERATORNAME FROM report_terminal_personal";
		String soucreFileName = "/excelTemplate/report_personal.xls";
		int col = 17;// excel包含的列数
		int[] arr = null;// 需要统计的列
		int sheetIndex = 0;// 数据展示的表格
		int rowIndex = 5;// 开始显示数据的行
		int columnIndex = 0;// 开始显示数据的列
		// excel名称
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		String path = emailSourceSetting.getFilepath();
		StringBuffer s = new StringBuffer();
		//String fileAttach="/report-person-zdd-";
		String fileAttach="/O2O金融个人终端贷月度余额明细表-";
		String outPath = s.append(path).append("/"+date).append(fileAttach).append(date).append(".xls").toString();
		Calendar calendar = Calendar.getInstance();
		String dateString = "截止日期：" + calendar.get(Calendar.YEAR)+"年"+ calendar.get(Calendar.MONTH) + "月末";
		write(sql, soucreFileName, outPath, col, arr, Arrays.asList("终端贷授信情况汇总表", dateString), sheetIndex, rowIndex, columnIndex);
		//step3
		return path+"/"+date;
	}
	@Deprecated
	public String reportSheng() {
		//脚本
		String sql = "sql.AdapterDataService.reportSheng";
		//模板
		String soucreFileName = "/excelTemplate/excel-sheng-report.xls";
		//输出路径
		String path = emailSourceSetting.getFilepath();
		StringBuffer s = new StringBuffer();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		//标题
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		
		String fileAttach="/省区明细-";
		String outPath = s.append(path).append("/"+date).append(fileAttach).append(yesterday).append(".xls").toString();
		//包含的列
		int col = 13;
		//需要统计的列
		int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11,12 };
		String title = "O2O金融业务台账明细（截止到" + yesterday + "）";
		//开始的表格，行，列
		int sheetIndex=0;
		int rowIndex=3;
		int columnIndex=0;
		//生产excel
		write(sql, soucreFileName, outPath, col,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		
		//存放的文件的文件夹
		return path+"/"+date;
	}

	@Deprecated
	public String reportShengByDate(String Date) {
		//脚本
		String sql = "sql.AdapterDataService.reportShengByDate";
		//模板
		String soucreFileName = "/excelTemplate/excel-sheng-report.xls";
		//输出路径
		String path = emailSourceSetting.getFilepath();
		StringBuffer s = new StringBuffer();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		//标题
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		
		String fileAttach="/省区明细-";
		String outPath = s.append(path).append("/"+Date+"~"+date).append(fileAttach).append(yesterday).append(".xls").toString();
		//包含的列
		int col = 13;
		//需要统计的列
		int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11,12 };
		String title = "O2O金融业务台账明细（"+Date+"至"+yesterday+"）";
		//开始的表格，行，列
		int sheetIndex=0;
		int rowIndex=3;
		int columnIndex=0;
		//生产excel
		writeByDate(sql, soucreFileName, outPath, col,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex,Date);
		//存放的文件的文件夹
		return path+"/"+date;
	}
	/*
	 * 生成excel
	 * 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
	 */
	@Deprecated
	public void writeByDate(String sql, String soucreFileName, String outPath, int col,int[] arr,List<String> title,int sheetIndex,int rowIndex,int columnIndex,String Date) {
		List<Object[]> data = baseReportService.findObjectsList(sql,Date);
		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath), ProcessMonitorReportService.class.getResourceAsStream(soucreFileName), new BankRowWriteHandler(data, col,arr,title,sheetIndex,rowIndex,columnIndex));
			
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}
	
	
	
	@Deprecated
	public String reportPingTai() {
		//脚本
		String sql = "sql.AdapterDataService.reportPingTai";
		//模板
		String soucreFileName = "/excelTemplate/excel-pingtai-report.xls";
		//输出路径
		String path = emailSourceSetting.getFilepath();
		StringBuffer s = new StringBuffer();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		//标题
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		
		String fileAttach="/平台明细-";
		String outPath = s.append(path).append("/"+date).append(fileAttach).append(yesterday).append(".xls").toString();
		//包含的列
		int col = 14;
		//需要统计的列
		int[] arr = { 2, 3, 4, 5, 6, 7, 8, 9, 10,11,12,13 };
		String title = "O2O金融业务台账明细（截止到" + yesterday + "）";
		//开始的表格，行，列
		int sheetIndex=0;
		int rowIndex=3;
		int columnIndex=0;
		//生产excel
		write(sql, soucreFileName, outPath, col,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		//存放的文件的文件夹
		return path+"/"+date;
	}
	
	/**
	 * 生成银行报表
	 */
	@Deprecated
	public String  reportBank() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		
		String excuteWriteReportBank = "sql.AdapterDataService.reportBank";
		String soucreFileName = "/excelTemplate/excel-bank-report.xls";
		
		String filename = "放款银行明细-" + yesterday + ".xls";
		String filePath = emailSourceSetting.getFilepath()+"/"+date+ "/" + filename;
		
		int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11 ,12,13,14};
		
		String title = "O2O金融业务台账明细（截止到" + yesterday + "单位/人民币元，笔数）";
		//excuteProReport(runProReportBank);
		int sheetIndex=0;
		int rowIndex=4;
		int columnIndex=0;
		write(excuteWriteReportBank, soucreFileName, filePath, 15,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		return emailSourceSetting.getFilepath()+"/"+date;
	}

	/*
	 * 整合报表数据
	 */
	@Deprecated
	public void excuteProReport(String pro) {
		baseReportService.excuteProReport(pro);
	}


	

	/**
	 * 生成银行报表
	 * 
	 * @param sql
	 * @param soucreFileName
	 * @param outPath
	 * @param col
	 * @param arr
	 * @param title
	 */
	public void writeTotle(String sql, String soucreFileName, String outPath, int col, int[] arr, String title) {
		List<Object[]> data = baseReportService.findObjectsList(sql);
		double sumBjyq = 0;// 本金逾期总额
		double sumBjdc = 0;// 本金代偿总额
		double sumLxyq = 0;// 利息逾期总额
		double sumFkze = 0;// 放款总额
		double sumFkye = 0;// 余额总额
		StringBuffer total = new StringBuffer();
		total.append("已到期风险比率为");
		for (int r = 0; r < data.size(); r++) {
			for (int i = 0; i < data.get(r).length; i++) {
				if (null != data.get(r)[i]) {
					if (i == 2)
						sumFkze += Double.parseDouble(data.get(r)[i].toString());
					if (i == 4)
						sumFkye += Double.parseDouble(data.get(r)[i].toString());
					if (i == 6)
						sumBjyq += Double.parseDouble(data.get(r)[i].toString());
					if (i == 8)
						sumBjdc += Double.parseDouble(data.get(r)[i].toString());
					if (i == 10)
						sumLxyq += Double.parseDouble(data.get(r)[i].toString());
				}
			}
		}
		double fxl = ((sumBjyq + sumBjdc + sumLxyq) / sumFkze) * 100;// 根据提供的公式计算出已到期风险率
		double cxl = ((sumBjyq + sumBjdc + sumLxyq) / (sumBjyq + sumBjdc + sumLxyq + sumFkye)) * 100;// 根据提供的公式计算出出险率
		DecimalFormat df = new DecimalFormat("######0.00");// 计算结果保留两位小数
		df.format(fxl);
		df.format(cxl);
		total.append(df.format(fxl));
		total.append("%");
		total.append("\n");
		total.append("   出险率为");
		total.append(df.format(cxl));
		total.append("%");
		int sheetIndex=0;
		int rowIndex=3;
		int columnIndex=0;
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath), ProcessMonitorReportService.class.getResourceAsStream(soucreFileName), new BankRowWriteHandler(data, col, arr, Arrays.asList(title,total.toString()),sheetIndex,rowIndex,columnIndex));
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}

	/*
	 * 发送邮件
	 */
	@Deprecated 
	public void mailSend(ArrayList<String> listDirectories,String title,String date,boolean flag) {
		EmailContentEntity entity = new EmailContentEntity();
		entity.setHost(emailSourceSetting.getHost());
		entity.setUsername(emailSourceSetting.getFrom());
		entity.setPassword(emailSourceSetting.getPassword());
		entity.setFrom(emailSourceSetting.getFrom());
		entity.setSubject(title + date);
		entity.setContent(title + date);
		if(flag){
			entity.setTo(emailSourceSetting.getTo());
			//list去重
			HashSet<String> h = new  HashSet<String>(listDirectories);
			listDirectories.clear(); 
			listDirectories.addAll(h); 
			entity.setAttachmentDirectories(listDirectories);
		}else{
			entity.setTo(devmail);
		}
		emailSender.send(entity);
	}
	
	/*
	 * 发送邮件,一个一个人的发送<br>
	 * 避免因为某个人的邮箱失效导致所有人都无法成功接受邮件
	 */
	public void mailSendOneByOne(ArrayList<String> listDirectories,String title,String date,boolean flag) {
		EmailContentEntity entity = new EmailContentEntity();
		entity.setHost(emailSourceSetting.getHost());
		entity.setUsername(emailSourceSetting.getFrom());
		entity.setPassword(emailSourceSetting.getPassword());
		entity.setFrom(emailSourceSetting.getFrom());
		entity.setSubject(title + date);
		entity.setContent(title + date);
		
		String[] receivers = emailSourceSetting.getTo().split(",");
		if(flag){
			//list去重
			HashSet<String> h = new  HashSet<String>(listDirectories); 
			listDirectories.clear(); 
			listDirectories.addAll(h); 
			entity.setAttachmentDirectories(listDirectories);
		}else{
			receivers = devmail.split(",");
		}
		
		for(String receiver : receivers){
			entity.setTo(receiver);
			emailSender.send(entity);
		}
		
	}

	
	
	/*
	 * 
	 * 发送邮件,一个一个人的发送<br>
	 * 避免因为某个人的邮箱失效导致所有人都无法成功接受邮件
	 */
	public void mailSendOneByOneNew(ArrayList<String> listDirectories,String title,String date,boolean flag) {
		MailNotifyRequest mailNotifyRequest=new MailNotifyRequest();
		mailNotifyRequest.setTitle(title + date);
		mailNotifyRequest.setContent(title + date);
		mailNotifyRequest.setSystemCode("报表系统");
		mailNotifyRequest.setMailSendType(MailSendType.INSTANT);
		mailNotifyRequest.setRequestId(UuidUtil.generateShortUuid());
		mailNotifyRequest.setRequestDateTime(new Date());
		mailNotifyRequest.setBizCode("日报报表");
		String[] receivers = emailSourceSetting.getTo().split(",");
		if(flag){
			//list去重
			HashSet<String> h = new  HashSet<String>(listDirectories); 
			listDirectories.clear(); 
			listDirectories.addAll(h); 
			if(listDirectories!=null&&listDirectories.size()>0){
				mailNotifyRequest.setAttachmentPath(listDirectories.toString().substring(1, listDirectories.toString().length()-1));
			}
		}else{
			receivers = devmail.split(",");
		}
		ModelResult<MailNotifyResponse> model=null;
		for(String receiver : receivers){
			mailNotifyRequest.setRecipientTo(receiver);
			try {
				 model=mailNotifier.notify(mailNotifyRequest);
				 if(model.isSuccess()){
					 logger.info("邮件查库完成", model.getModel().getResponseId());
				 }else{
					 logger.error("邮件发送异常，异常code为：[{}],异常内容为：[{}]", model.getErrCode(),model.getErrMsg());
				 }
			} catch (Exception e) {
				logger.error("邮件发送接口调用异常",e.getMessage());
			}
		}
		
		
	}
	/**
	 * @Title: refundSendEmial
	 * @Description: 放款发邮件
	 * @author 张庆奇
	 * @param flag
	 * @return void
	 * @throws
	 * @date 2016年8月1日 下午2:42:53
	 */
	@Deprecated
	public void refundSendEmial(boolean flag) {
		
		String title="日放款数据-";
		String date=DateUtil.getSpecifiedDayBefore(0);//今天时间
		ArrayList<String> listDirectories=new ArrayList<String>();
		listDirectories.add(emailSourceSetting.getFilepath()+"/refund/"+date); 
		
		MailNotifyRequest mailNotifyRequest=new MailNotifyRequest();
		mailNotifyRequest.setTitle(title + date);
		mailNotifyRequest.setContent(title + date);
		mailNotifyRequest.setSystemCode("报表系统");
		mailNotifyRequest.setMailSendType(MailSendType.INSTANT);
		mailNotifyRequest.setRequestId(UuidUtil.generateShortUuid());
		mailNotifyRequest.setRequestDateTime(new Date());
		mailNotifyRequest.setBizCode("日放款数据报表");
		String[] receivers = emailSourceSetting.getTo().split(",");
		if(flag){
			//list去重
			HashSet<String> h = new  HashSet<String>(listDirectories); 
			listDirectories.clear(); 
			listDirectories.addAll(h); 
			if(listDirectories!=null&&listDirectories.size()>0){
				mailNotifyRequest.setAttachmentPath(listDirectories.toString().substring(1, listDirectories.toString().length()-1));
			}
		}else{
			receivers = devmail.split(",");
		}
		ModelResult<MailNotifyResponse> model=null;
		for(String receiver : receivers){
			mailNotifyRequest.setRecipientTo(receiver);
			try {
				 model=mailNotifier.notify(mailNotifyRequest);
				 if(model.isSuccess()){
					 logger.info("邮件发送完成，插库信息为：[{}]", model.getModel().getResponseId());
				 }else{
					 logger.error("邮件发送异常，异常code为：[{}],异常内容为：[{}]", model.getErrCode(),model.getErrMsg());
				 }
			} catch (Exception e) {
				logger.error("邮件发送接口调用异常",e.getMessage());
			}
		}
	}

	/*
	 * 生成excel
	 * 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
	 */
	@Deprecated
	public void writeNewBusiness(String sql, String soucreFileName, String outPath, int col,int[] arr,List<String> title,int sheetIndex,int rowIndex,int columnIndex) {
		String nowday=DateUtil.getSpecifiedDayBefore(0);//今天时间
		List<Object[]> data = baseReportService.findObjectsList(sql,nowday);
		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath), ProcessMonitorReportService.class.getResourceAsStream(soucreFileName), new BankRowWriteHandler(data, col,arr,title,sheetIndex,rowIndex,columnIndex));
			
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}
	
	@Deprecated
	public void addData(String sql, String soucreFileName, String outPath, int col,int[] arr,List<String> title,int sheetIndex,int rowIndex,int columnIndex) {
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		List<Object[]> data = baseReportService.findObjectsList(sql,yesterday);

		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath), ProcessMonitorReportService.class.getResourceAsStream(soucreFileName), new BankRowWriteHandler(data, col,arr,title,sheetIndex,rowIndex,columnIndex));
			
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}
	
	
	
	/**
	 * 备份新增业务数据报表
	 * @param sql
	 */
	@Deprecated
	public void insertReportNewBusiness(String sql) {
		String format = "yyyyMMdd";
		SimpleDateFormat df = new SimpleDateFormat(format);
		String current = df.format(new Date());
		Date toDate = null;
		Date fromDate = null;
		try {
			toDate = df.parse(current);
			fromDate = new Date(toDate.getTime() - (24 * 60 * 60 * 1000));
		} catch (Exception e) {

		}
		Date yesterday = fromDate;// 昨天时间
		Date before = toDate;// 前天时间
	    Date params []= {yesterday,before,yesterday,before,yesterday,before,yesterday,before,yesterday,before,yesterday,before,yesterday,before};
	    //baseReportService.isnertNewBusiness(sql,params);
	}

	/**
	 * 删除统计日的久数据
	 * @param sql
	 */
	@Deprecated
	public void deleteNewBusiness(String sql,String param){
		baseReportService.deleteNewBusiness(sql,param);
	}
	
	/**
	 * 生成新增业务数据报表
	 */
	@Deprecated
	public String reportNewBusiness() {
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		String nowday=DateUtil.getSpecifiedDayBefore(0);//今天时间
	    String sql = "sql.AdapterDataService.reportNewBusiness";
	    String insertSql = "sql.AdapterDataService.isnertNewBusiness";
	    String deleteSql = "sql.AdapterDataService.deleteNewBusiness";
	    deleteNewBusiness(deleteSql, nowday);//删除统计日旧数据
	    insertReportNewBusiness(insertSql);//插入统计日数据
		String soucreFileName = "/excelTemplate/report_new_business.xls";
		String filename_up = "新增业务统计-";
		String filename_ = yesterday + ".xls";
		String filename = filename_up + filename_;
		String path=emailSourceSetting.getFilepath();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		String filePath = path +"/"+date +"/" + filename;
		String title = yesterday + "日业务新增数据统计报表";
		int[] arr = {1, 2, 3, 4, 5, 6,7};
		int sheetIndex=0;
		int rowIndex=2;
		int columnIndex=0;
		writeNewBusiness(sql, soucreFileName, filePath, 8,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		return path+"/"+date;
	}
	
	/**
	 * @Title: addData
	 * @Description: 生产放款数据
	 * @author 张庆奇
	 * @return
	 * @return String
	 * @throws
	 * @date 2016年8月1日 下午2:37:06
	 */
	@Deprecated
	public String addData(){
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		 String sql = "sql.AdapterDataService.addData";
			String soucreFileName = "/excelTemplate/excel-add-data.xls";
			String filename_up = "日放款数据(";
			String filename_ = yesterday + ").xls";
			String filename = filename_up + filename_;
			String path=emailSourceSetting.getFilepath();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String date = sdf.format(new Date());
			String filePath = path +"/refund/"+date +"/" + filename;
			
			int[] arr = {};
			int sheetIndex=0;
			int rowIndex=1;
			int columnIndex=1;
			addData(sql, soucreFileName, filePath, 8,arr,Arrays.asList(""),sheetIndex,rowIndex,columnIndex);
			
			return path+"/refund/"+date;
	}
	
	
	
	/**
	 * @Title: rerundBankReport
	 * @Description: 管理层日报一期-合作银行放款台账 
	 * @author 张庆奇
	 * @return
	 * @return String
	 * @throws
	 * @date 2016年12月26日 下午2:03:25
	 */
	@Deprecated
	public String  rerundBankReport() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		
		String excuteWriteReportBank = "sql.AdapterDataService.rerundBankReport";
		String soucreFileName = "/excelTemplate/excel-refund-bank-report.xls";
		
		String filename = "合作银行放款台账一期-" + yesterday + ".xls";
		String filePath = emailSourceSetting.getFilepath()+"/"+date+ "/" + filename;
		
		int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8, 9};
		
		String title = "合作银行放款台账（" + yesterday + "）单位:万元";	
		int sheetIndex=0;
		int rowIndex=3;
		int columnIndex=0;
		write(excuteWriteReportBank, soucreFileName, filePath, 10,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		return emailSourceSetting.getFilepath()+"/"+date;
	}
	
	
	/**
	 * @Title: progressReport
	 * @Description: 业务精度报表
	 * @author 张庆奇
	 * @return
	 * @return String
	 * @throws
	 * @date 2016年12月30日 下午5:21:13
	 */
	@Deprecated
	public String  progressReport() {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		
		String excuteWriteReportBank = "sql.AdapterDataService.progressReport";
		String soucreFileName = "/excelTemplate/excel-progress-report.xls";
		
		String filename = "业务进度日报表一期" + yesterday + ".xls";
		String filePath = emailSourceSetting.getFilepath()+"/"+date+ "/" + filename;
		
		int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10,11 ,12,13,14,15,16,17,18,19,20};
		
		String title = "业务进度日报表（" + yesterday + "）单位:万元";	
		int sheetIndex=0;
		int rowIndex=4;
		int columnIndex=0;
		write(excuteWriteReportBank, soucreFileName, filePath, 21,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
		return emailSourceSetting.getFilepath()+"/"+date;
	}

	
	
	
	
	/*
	 * 生成excel
	 * 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
	 */
	@Deprecated
	public void write(String sql, String soucreFileName, String outPath, int col,int[] arr,List<String> title,int sheetIndex,int rowIndex,int columnIndex) {
		List<Object[]> data = baseReportService.findObjectsList(sql);
		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath), ProcessMonitorReportService.class.getResourceAsStream(soucreFileName), new BankRowWriteHandler(data, col,arr,title,sheetIndex,rowIndex,columnIndex));
			
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}
	
	
	
	
	

	/**
	 * @Title: loanDetail
	 * @Description: 生成O2O金融业务台账——投放量统计表（按省区-平台-借款人明细表）报表
	 * @author 张庆奇
	 * @return
	 * @return String
	 * @throws
	 * @date 2016年8月19日 上午9:07:20
	 */
	@Deprecated
	public String loanDetail(){
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		 String sql = "sql.AfterLoanDataService.loanDetail";
			String soucreFileName = "/excelTemplate/excel-loan-detail-data.xls";
			String filename_up = "投放量统计表（按省区-平台-借款人明细表）";
			String filename_ = ".xls";
			String filename = filename_up + filename_;
			String path=emailSourceSetting.getFilepath();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String date = sdf.format(new Date());
			String filePath = path +"/"+date +"/" + filename;
			String title="O2O金融业务台账——投放量统计表（按省区-平台-借款人明细表）" ;
			int[] arr = {};
			int sheetIndex=0;
			int rowIndex=3;
			int columnIndex=0;
			write(sql, soucreFileName, filePath, 21,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
			
			return path+"/"+date;
	}
	
	
	/**
	 * @Title: reportPingtaiDelivery
	 * @Description: O2O金融业务台账——投放量统计表（按平台)
	 * @author 张庆奇
	 * @return
	 * @return String
	 * @throws
	 * @date 2016年8月19日 上午9:07:20
	 */
	@Deprecated
	public String reportPingtaiDelivery(){
		String yesterday=DateUtil.getSpecifiedDayBefore(1);//昨天时间
		 String sql = "sql.AfterLoanDataService.report_pingtai_delivery";
			String soucreFileName = "/excelTemplate/excel-pingtai-delivery-data.xls";
			String filename_up = "投放量统计表（按平台)";
			String filename_ = ".xls";
			String filename = filename_up + filename_;
			String path=emailSourceSetting.getFilepath();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String date = sdf.format(new Date());
			String filePath = path +"/"+date +"/" + filename;
			String title="O2O金融业务台账明细（截止到"+ yesterday +"，单位/人民币元，笔）" ;
			int[] arr = {};
			int sheetIndex=0;
			int rowIndex=4;
			int columnIndex=0;
			write(sql, soucreFileName, filePath, 17,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
			
			return path+"/"+date;
	}
	
	
   
	/*
	 * 生成excel(O2O金融业务台账——借款人账务信息明细表)
	 */
	@Deprecated
	public String loanFinanceInfo(){
		String yesterday=DateUtil.getSpecifiedDayBefore(1);
		 String sql = "sql.AfterLoanDataService.loanFinanceInfo";
			String soucreFileName = "/excelTemplate/loan-finance-info.xls";
			String filename_up = "借款人账务信息明细表";
			String filename_ = ".xls";
			String filename = filename_up + filename_;
			String path=emailSourceSetting.getFilepath();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String date = sdf.format(new Date());
			String filePath = path +"/"+date +"/" + filename;
			String title="O2O金融业务台账明细（截止到"+ yesterday +"，单位/人民币元）" ;
			int[] arr = {};
			int sheetIndex=0;
			int rowIndex=3;
			int columnIndex=0;
			write(sql, soucreFileName, filePath, 29,arr,Arrays.asList(title),sheetIndex,rowIndex,columnIndex);
			return path+"/"+date;
	}
	
	/**
	 * @Title: rerundProvinceReport
	 * @Description: 管理层日报一期-按省份统计 old
	 * @author 刘文祥
	 * @return
	 * @return String
	 * @throws @date
	 *             2016年12月26日 下午2:03:25
	 */
	@Deprecated
	public String rerundProvinceReport() {
		String soucreFileName = "/excelTemplate/refund-day-report.xls";
		String filename_up = "按省份统计放款日报表";
		String filename_ = ".xls";
		String filename = filename_up + filename_;
		String path = emailSourceSetting.getFilepath();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		String date = sdf.format(new Date());
		String filePath = path + "/" + date + "/" + filename;
		int[] arr = { 1, 2, 3, 4, 5, 6, 7, 8, 11, 12, 13, 14, 17, 18 };
		String yesterday = DateUtil.getSpecifiedDayBefore(1);// 昨天时间
		String title = "放款日报表（" + yesterday + "）     单位：  万元";
		int sheetIndex = 0;
		int rowIndex = 3;
		int columnIndex = 0;
		rerundProvinceWrite(soucreFileName, filePath, 19, arr, Arrays.asList(title), sheetIndex, rowIndex, columnIndex);
		return path + "/" + date;
	}

	/*
	 * 生成excel 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
	 */
	@Deprecated
	public void rerundProvinceWrite(String soucreFileName, String outPath, int col, int[] arr, List<String> title,
			int sheetIndex, int rowIndex, int columnIndex) {
		List<Object[]> datas = getRerundProvinceWrite();
		IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
		try {
			ExcelApiHelper.writeHandle(new FileOutputStream(outPath),
					ProcessMonitorReportService.class.getResourceAsStream(soucreFileName),
					new BankRowWriteHandler(datas, col, arr, title, sheetIndex, rowIndex, columnIndex));

		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new ControllerException(e);
		}
	}
	@Deprecated
	public List<Object[]> getRerundProvinceWrite() {
		logger.info("开始同步省区放款日报表");
		// 删除之前的数据
		baseReportService.isnertNewBusiness("delete from province_refund_report");

		String sql1 = "sql.refund.sql1";
		logger.info("插入放款日报表-省区明细字段开始......");
		try {
			baseReportService.isnertNewBusiness(sql1);
		} catch (Exception e) {
			logger.error("插入放款日报表-省区明细的省区字段出现异常......", e);
		}
		logger.info("插入放款日报表-省区明细的省区字段结束......");

		// 当日放款(笔数,金额)
		String sql2 = "sql.refund.sql2";
		try {
			List<Object[]> data2 = baseReportService.findObjectsList(sql2);
			for (int i = 0; i < data2.size(); i++) {// 更新报表每一列的数据
				Object[] b = data2.get(i);
				baseReportService.isnertNewBusiness(
						"update province_refund_report set day_loan_count = ?,day_loan_sum=? where province = ? ", b[1],
						b[2], b[0]);
			}
		} catch (Exception e) {
			logger.info(
					"BasicAfterLoanDataService.getRerundProvinceWrite：当日放款(笔数,金额)统计异常=========================================",
					e);
		}

		// 当日新客户放款(笔数,金额)
		String sql3 = "sql.refund.sql3";
		try {
			List<Object[]> data3 = baseReportService.findObjectsList(sql3);
			for (int i = 0; i < data3.size(); i++) {// 更新报表每一列的数据
				Object[] b = data3.get(i);
				baseReportService.isnertNewBusiness(
						"update province_refund_report set day_loan_count_new = ?,day_loan_sum_new=? where province = ? ",
						b[1], b[2], b[0]);
			}
		} catch (Exception e) {
			logger.info(
					"BasicAfterLoanDataService.getRerundProvinceWrite：当日新客户放款(笔数,金额)统计异常=========================================",
					e);
		}

		// 本月累计放款(笔数,金额)
		String sql4 = "sql.refund.sql4";
		try {
			List<Object[]> data4 = baseReportService.findObjectsList(sql4);
			for (int i = 0; i < data4.size(); i++) {// 更新报表每一列的数据
				Object[] b = data4.get(i);
				baseReportService.isnertNewBusiness(
						"update province_refund_report set month_loan_count = ?,month_loan_sum=? where province = ? ",
						b[1], b[2], b[0]);
			}
		} catch (Exception e) {
			logger.info(
					"BasicAfterLoanDataService.getRerundProvinceWrite：本月累计放款(笔数,金额)统计异常=========================================",
					e);
		}

		// 本月新客户累计放款(笔数,金额)
		String sql5 = "sql.refund.sql5";
		try {
			List<Object[]> data5 = baseReportService.findObjectsList(sql5);
			for (int i = 0; i < data5.size(); i++) {// 更新报表每一列的数据
				Object[] b = data5.get(i);
				baseReportService.isnertNewBusiness(
						"update province_refund_report set month_loan_count_new = ?,month_loan_sum_new=? where province = ? ",
						b[1], b[2], b[0]);
			}
		} catch (Exception e) {
			logger.info(
					"BasicAfterLoanDataService.getRerundProvinceWrite：本月新客户累计放款(笔数,金额)统计异常=========================================",
					e);
		}

		// 本年累计放款(笔数,金额)
		String sql6 = "sql.refund.sql6";
		try {
			List<Object[]> data6 = baseReportService.findObjectsList(sql6);
			for (int i = 0; i < data6.size(); i++) {// 更新报表每一列的数据
				Object[] b = data6.get(i);
				baseReportService.isnertNewBusiness(
						"update province_refund_report set year_loan_count = ?,year_loan_sum=? where province = ? ",
						b[1], b[2], b[0]);
			}
		} catch (Exception e) {
			logger.info(
					"BasicAfterLoanDataService.getRerundProvinceWrite：本年累计放款(笔数,金额)统计异常=========================================",
					e);
		}

		// 本年新客户累计放款(笔数,金额)
		String sql7 = "sql.refund.sql7";
		try {
			List<Object[]> data7 = baseReportService.findObjectsList(sql7);
			for (int i = 0; i < data7.size(); i++) {// 更新报表每一列的数据
				Object[] b = data7.get(i);
				baseReportService.isnertNewBusiness(
						"update province_refund_report set year_loan_count_new = ?,year_loan_sum_new=? where province = ? ",
						b[1], b[2], b[0]);
			}
		} catch (Exception e) {
			logger.info(
					"BasicAfterLoanDataService.getRerundProvinceWrite：本年新客户累计放款(笔数,金额)统计异常=========================================",
					e);
		}

		// 本月新客户占比(笔数,金额)
		String sql8 = "sql.refund.sql8";
		try {
			List<Object[]> data8 = baseReportService.findObjectsList(sql8);
			for (int i = 0; i < data8.size(); i++) {// 更新报表每一列的数据
				Object[] b = data8.get(i);
				baseReportService.isnertNewBusiness(
						"update province_refund_report set month_percent_count = ?,month_percent_sum=?,year_percent_count = ?,year_percent_sum=? where province = ? ",
						b[1], b[2], b[3], b[4], b[0]);
			}
		} catch (Exception e) {
			logger.info(
					"BasicAfterLoanDataService.getRerundProvinceWrite：本月新客户占比(笔数,金额)统计异常=========================================",
					e);
		}

		// 当日贷款余额,当日贷款款余额较年初增长金额
		String sql9 = "sql.refund.sql9";
		try {
			List<Object[]> data9 = baseReportService.findObjectsList(sql9);
			for (int i = 0; i < data9.size(); i++) {// 更新报表每一列的数据
				Object[] b = data9.get(i);
				baseReportService.isnertNewBusiness(
						"update province_refund_report set day_loan_after = ?  where province = ? ", b[1], b[0]);
			}
		} catch (Exception e) {
			logger.info(
					"BasicAfterLoanDataService.getRerundProvinceWrite：当日贷款余额,当日贷款款余额较年初增长金额统计异常=========================================",
					e);
		}

		// 当日贷款余额,当日贷款款余额较年初增长金额
		String sql10 = "sql.refund.sql10";
		try {
			List<Object[]> data10 = baseReportService.findObjectsList(sql10);
			for (int i = 0; i < data10.size(); i++) {// 更新报表每一列的数据
				Object[] b = data10.get(i);
				baseReportService.isnertNewBusiness(
						"update province_refund_report set day_loan_up = ?  where province = ? ", b[1], b[0]);
			}
		} catch (Exception e) {
			logger.info(
					"BasicAfterLoanDataService.getRerundProvinceWrite：当日贷款余额,当日贷款款余额较年初增长金额统计异常=========================================",
					e);
		}

		baseReportService
				.isnertNewBusiness("update province_refund_report set day_loan_up = day_loan_after - day_loan_up");

		String sql11 = "sql.refund.sql11";
		List<Object[]> datas = baseReportService.findObjectsList(sql11);
		logger.info("结束同步省区放款日报表");
		return datas;
	}

	
	
	

		/*
		 * 生成excel 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
		 */
		public void write(String sql, String soucreFileName, String outPath, int col, int[] arr, List<String> title,
				int sheetIndex, int rowIndex, int columnIndex, String beginDate, String endDate) {
			List<Object[]> datas = new ArrayList<Object[]>();
			// 省份
			// String[]
			// a={"安徽","北京","福建","广东","广西","贵州","河北","河南","黑龙江","湖北","湖南","吉林","江苏","江西","辽宁","山东","山西","陕西","上海","四川","天津","云南","浙江","重庆"};
			// String[]
			// a={"安徽","山东","福建","广西","湖北","贵州","江苏","河南","河北","北京","重庆","四川","上海","云南","辽宁","江西","黑龙江","吉林","山西"};
			List<String> l = new ArrayList<>();
			String sql1 = "sql.BasicAfterLoanDataService.sql1";
			List<Object[]> data1 = baseReportService.findObjectsList(sql1);
			for (int i = 0; i < data1.size(); i++) {// 更新报表每一列的数据
				Object[] b = data1.get(i);
				if (b != null) {
					String province = (String) b[0];
					l.add(province);
				}
			}
			Object[] a = l.toArray();
			logger.info("开始同步月度报表");
			for (int i = 0; i < a.length; i++) {// 循环每个省区
				Object[] b = new Object[28];
				List<Object[]> data = baseReportService.findObjectsList(sql, beginDate, endDate, a[i]);// 一个省份的数据
				b[0] = i + 1;// 排序
				b[1] = a[i];// 省份
				int count = 1;
				if (data != null && data.size() > 0) {// 该省区有数据
					String[] date = { "201601", "201602", "201603", "201604", "201605", "201606", "201607", "201608",
							"201609", "201610", "201611", "201612" };// 日期
					Long counts = 0l;
					BigDecimal sums = new BigDecimal(0);
					boolean flag = true;
					for (int k = 0; k < date.length; k++) {// 循环每个月
						// 有数据，填充数据
						for (int j = 0; j < data.size(); j++) {
							Object[] rowData = data.get(j);// 每个省区每个月份的数据
							if (rowData != null && rowData[1].equals(date[k])) {
								b[++count] = rowData[2];
								b[++count] = rowData[3];
								counts = counts + (Long) rowData[2];
								sums = sums.add((BigDecimal) rowData[3]);
								flag = false;
							}
						}
						// 没数据，补0
						if (flag) {
							b[++count] = "0";
							b[++count] = "0";
						}
						flag = true;
					}
					// 合计
					b[26] = counts;
					b[27] = sums;
				} else {// 该省区无数据
					for (int j = 1; j < 28; j++) {
						b[j] = "0";
					}
				}
				datas.add(b);
			}
			logger.info("结束同步月度报表");

			IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
			try {
				ExcelApiHelper.writeHandle(new FileOutputStream(outPath),
						ProcessMonitorReportService.class.getResourceAsStream(soucreFileName),
						new BankRowWriteHandler(datas, col, arr, title, sheetIndex, rowIndex, columnIndex));

			} catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new ControllerException(e);
			}
		}

		// 按省分组进行月度统计
		@Deprecated
		public String monthDetail() {
			String beginDate = "2015-12-31";
			String endDate = "2017-01-01";
			String sql = "sql.AfterLoanDataService.monthDetail";
			String soucreFileName = "/excelTemplate/excel-loan-month-data.xls";
			String filename_up = "投月度统计表（按省区-月度-明细表）";
			String filename_ = ".xls";
			String filename = filename_up + filename_;
			String path = emailSourceSetting.getFilepath();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String date = sdf.format(new Date());
			String filePath = path + "/" + date + "/" + filename;

			int[] arr = { 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27 };
			String yesterday = DateUtil.getSpecifiedDayBefore(1);// 昨天时间
			String title = "O2O金融业务台账明细（截止到" + yesterday + "）";
			int sheetIndex = 0;
			int rowIndex = 3;
			int columnIndex = 0;
			write(sql, soucreFileName, filePath, 28, arr, Arrays.asList(title), sheetIndex, rowIndex, columnIndex,
					beginDate, endDate);
			return path + "/" + date;
		}

		// 按省分组进行月度统计
		@Deprecated
		public String delivery() {
			String soucreFileName = "/excelTemplate/excel-delivery-data.xls";
			String filename_up = "数据投放量（按省区）";
			String filename_ = ".xls";
			String filename = filename_up + filename_;
			String path = emailSourceSetting.getFilepath();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			String date = sdf.format(new Date());
			String filePath = path + "/" + date + "/" + filename;
			int[] arr = { 2, 3, 4, 5, 6, 7, 8, 10, 11, 13, 14, 17, 18, 19, 20, 22, 23, 25, 26, 28, 29, 30, 31, 32 };
			String yesterday = DateUtil.getSpecifiedDayBefore(1);// 昨天时间
			String title = "O2O金融业务台账明细（截止到" + yesterday + "单位/人民币元，笔）";
			int sheetIndex = 0;
			int rowIndex = 4;
			int columnIndex = 0;
			write(soucreFileName, filePath, 33, arr, Arrays.asList(title), sheetIndex, rowIndex, columnIndex);
			return path + "/" + date;
		}

		/*
		 * 生成excel 参数：数据，源文件，输出文件，excel包含的列，需要统计的列，excel标题
		 */
		public void write(String soucreFileName, String outPath, int col, int[] arr, List<String> title, int sheetIndex,
				int rowIndex, int columnIndex) {
			List<Object[]> datas = getDeliveryData();
			IoUtil.createDirectorieIfMissing(new File(outPath).getParentFile());
			try {
				ExcelApiHelper.writeHandle(new FileOutputStream(outPath),
						ProcessMonitorReportService.class.getResourceAsStream(soucreFileName),
						new BankRowWriteHandler(datas, col, arr, title, sheetIndex, rowIndex, columnIndex));

			} catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw new ControllerException(e);
			}
		}

		private List<Object[]> getDeliveryData() {
			logger.info("开始同步按省区投放量报表");
			// 删除之前的数据
			baseReportService.isnertNewBusiness("delete from report_sheng_delivery");
			// 省份,下辖平台总量
			String sql1 = "sql.BasicAfterLoanDataService.sql1";
			int count = 0;
			try {
				List<Object[]> data1 = baseReportService.findObjectsList(sql1);
				for (int i = 0; i < data1.size(); i++) {// 更新报表每一列的数据
					count++;
					Object[] b = data1.get(i);
					if (b != null) {
						String province = (String) b[0];
						baseReportService.isnertNewBusiness("insert into report_sheng_delivery(id,province) values(?,?)",
								count, province);
						List<ActOrgEntity> list = sysOrgService.getChildOrgByName(province);
						if (list != null && list.size() > 0) {
							baseReportService.isnertNewBusiness(
									"update report_sheng_delivery set under_ltd = ? where province = ? ", list.size() - 1,
									province);
						}
					}
				}
			} catch (Exception e) {
				logger.info("BasicAfterLoanDataService：省份,下辖平台总量统计异常=========================================");
			}

			// 本年激活平台数量
			String sql2 = "sql.BasicAfterLoanDataService.sql2";
			try {
				List<Object[]> data2 = baseReportService.findObjectsList(sql2);
				for (int i = 0; i < data2.size(); i++) {// 更新报表每一列的数据
					Object[] b = data2.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set active_ltd_year = ? where province = ? ", b[1], b[0]);
				}
			} catch (Exception e) {
				logger.info("BasicAfterLoanDataService：本年激活平台数量统计异常=========================================");
			}

			// 本月激活平台数量
			String sql3 = "sql.BasicAfterLoanDataService.sql3";
			try {
				List<Object[]> data3 = baseReportService.findObjectsList(sql3);
				for (int i = 0; i < data3.size(); i++) {// 更新报表每一列的数据
					Object[] b = data3.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set active_ltd_month = ? where province = ? ", b[1], b[0]);
				}
			} catch (Exception e) {
				logger.info("BasicAfterLoanDataService：本月激活平台数量统计异常=========================================");
			}

			// 本年累计放款量笔数,本年累计放款量金额,本年累计放款量笔均金额
			String sql4 = "sql.BasicAfterLoanDataService.sql4";
			try {
				List<Object[]> data4 = baseReportService.findObjectsList(sql4);
				for (int i = 0; i < data4.size(); i++) {// 更新报表每一列的数据
					Object[] b = data4.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set loan_num_year = ? ,loan_amount_year=? ,loan_amount_rage_year = ? where province = ? ",
							b[1], b[2], b[3], b[0]);
				}
			} catch (Exception e) {
				logger.info(
						"BasicAfterLoanDataService：本年累计放款量笔数,本年累计放款量金额,本年累计放款量笔均金额统计异常=========================================");
			}

			// 本年累计放款量比去年同期新增,本年累计放款量排名
			String sql5 = "sql.BasicAfterLoanDataService.sql5";
			try {
				List<Object[]> data5 = baseReportService.findObjectsList(sql5);
				for (int i = 0; i < data5.size(); i++) {// 更新报表每一列的数据
					Object[] b = data5.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set add_last_year = ?, rank_year=? where province = ? ", b[1],
							b[2], b[0]);
				}
			} catch (Exception e) {
				logger.info(
						"BasicAfterLoanDataService：本年累计放款量比去年同期新增,本年累计放款量排名 统计异常=========================================");
			}

			// 本年新客户投放笔数,本年新客户投放金额,本年新客户投放排名
			String sql6 = "sql.BasicAfterLoanDataService.sql6";
			try {
				List<Object[]> data6 = baseReportService.findObjectsList(sql6);
				for (int i = 0; i < data6.size(); i++) {// 更新报表每一列的数据
					Object[] b = data6.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set loan_num_new_cus_year = ?, loan_amount_new_cus_year=?,rank_new_cus_year=?  where province = ? ",
							b[1], b[2], b[3], b[0]);
				}
			} catch (Exception e) {
				logger.info(
						"BasicAfterLoanDataService：本年新客户投放笔数,本年新客户投放金额,本年新客户投放排名 统计异常=========================================");
			}

			// 本年续贷客户投放笔数,本年续贷客户投放金额,本年续贷客户投放排名
			String sql7 = "sql.BasicAfterLoanDataService.sql7";
			try {
				List<Object[]> data7 = baseReportService.findObjectsList(sql7);
				for (int i = 0; i < data7.size(); i++) {// 更新报表每一列的数据
					Object[] b = data7.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set loan_num_old_cus_year = ?, loan_amount_old_cus_year=?,rank_old_cus_year=?  where province = ? ",
							b[1], b[2], b[3], b[0]);
				}
			} catch (Exception e) {
				logger.info(
						"BasicAfterLoanDataService：本年续贷客户投放笔数,本年续贷客户投放金额,本年续贷客户投放排名  统计异常=========================================");
			}

			// 本年新客户投放量占比%new_cus_rage_year
			String sql8 = "sql.BasicAfterLoanDataService.sql8";
			try {
				List<Object[]> data8 = baseReportService.findObjectsList(sql8);
				for (int i = 0; i < data8.size(); i++) {// 更新报表每一列的数据
					Object[] b = data8.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set new_cus_rage_year = ? where province = ? ", b[1], b[0]);
				}
			} catch (Exception e) {
				logger.info("BasicAfterLoanDataService：本年新客户投放量占比统计异常=========================================");
			}

			// 本月累计放款量笔数,本月累计放款量金额,本月累计放款量笔均金额
			String sql9 = "sql.BasicAfterLoanDataService.sql9";
			try {
				List<Object[]> data9 = baseReportService.findObjectsList(sql9);
				for (int i = 0; i < data9.size(); i++) {// 更新报表每一列的数据
					Object[] b = data9.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set loan_num_month = ? ,loan_amount_month=? ,loan_amount_rage_month = ? where province = ? ",
							b[1], b[2], b[3], b[0]);
				}
			} catch (Exception e) {
				logger.info(
						"BasicAfterLoanDataService：本月累计放款量笔数,本月累计放款量金额,本月累计放款量笔均金额统计异常=========================================");
			}

			// 本月累计放款量比去年同期新增,本月累计放款量排名
			String sql10 = "sql.BasicAfterLoanDataService.sql10";
			try {
				List<Object[]> data10 = baseReportService.findObjectsList(sql10);
				for (int i = 0; i < data10.size(); i++) {// 更新报表每一列的数据
					Object[] b = data10.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set add_last_month = ?, rank_month=? where province = ? ", b[1],
							b[2], b[0]);
				}
			} catch (Exception e) {
				logger.info(
						"BasicAfterLoanDataService：本月累计放款量比去年同期新增,本月累计放款量排名 统计异常=========================================");
			}

			// 本月新客户投放笔数,本月新客户投放金额,本月新客户投放排名
			String sql11 = "sql.BasicAfterLoanDataService.sql11";
			try {
				List<Object[]> data11 = baseReportService.findObjectsList(sql11);
				for (int i = 0; i < data11.size(); i++) {// 更新报表每一列的数据
					Object[] b = data11.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set loan_num_new_cus_month = ?, loan_amount_new_cus_month=?,rank_new_cus_month=?  where province = ? ",
							b[1], b[2], b[3], b[0]);
				}
			} catch (Exception e) {
				logger.info(
						"BasicAfterLoanDataService：本月新客户投放笔数,本月新客户投放金额,本月新客户投放排名统计异常=========================================");
			}

			// 本月续贷客户投放笔数,本月续贷客户投放金额,本月续贷客户投放排名
			String sql12 = "sql.BasicAfterLoanDataService.sql12";
			try {
				List<Object[]> data12 = baseReportService.findObjectsList(sql12);
				for (int i = 0; i < data12.size(); i++) {// 更新报表每一列的数据
					Object[] b = data12.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set loan_num_old_cus_month = ?, loan_amount_old_cus_month=?,rank_old_cus_month=?  where province = ? ",
							b[1], b[2], b[3], b[0]);
				}
			} catch (Exception e) {
				logger.info(
						"BasicAfterLoanDataService：本月续贷客户投放笔数,本月续贷客户投放金额,本月续贷客户投放排名  统计异常=========================================");
			}

			// 报告期T-1日放款量
			String sql13 = "sql.BasicAfterLoanDataService.sql13";
			try {
				List<Object[]> data13 = baseReportService.findObjectsList(sql13);
				for (int i = 0; i < data13.size(); i++) {// 更新报表每一列的数据
					Object[] b = data13.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set day_add_num = ?, day_add_amount=?  where province = ? ", b[1],
							b[2], b[0]);
				}
			} catch (Exception e) {
				logger.info("BasicAfterLoanDataService：报告期T-1日放款量统计异常=========================================");
			}

			// 本年管理费累计收入,报告期风险补偿金保有量,报告期履约诚意金保有量
			String sql14 = "sql.BasicAfterLoanDataService.sql14";
			try {
				List<Object[]> data14 = baseReportService.findObjectsList(sql14);
				for (int i = 0; i < data14.size(); i++) {// 更新报表每一列的数据
					Object[] b = data14.get(i);
					baseReportService.isnertNewBusiness(
							"update report_sheng_delivery set rec_manger = ?, rec_risk=?,rec_deposite=?  where province = ? ",
							b[1], b[2], b[3], b[0]);
				}
			} catch (Exception e) {
				logger.info(
						"BasicAfterLoanDataService：本年管理费累计收入,报告期风险补偿金保有量,报告期履约诚意金保有量统计异常=========================================");
			}

			String sql15 = "sql.BasicAfterLoanDataService.sql15";
			List<Object[]> datas = baseReportService.findObjectsList(sql15);
			logger.info("结束同步按省区投放量报表");
			return datas;
		}
}
